<?php

/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

/**
 * Description of LigneVoyage
 *
 * @author Colin
 */
class LigneVoyage extends CI_Model{
    
    // Public est obligatoire pour permettre l'initialisation automatique de l'objet
    // lorsque l'on récupère des résultats de la base de données.
    public $id;
    public $dateDep="";
    public $dureeJours="";
    public $prixUnit="";
    public $nbPlaces="";
    public $id_voyage="";

    function __construct()
    {
        parent::__construct();
    }
    
    
    
    function getLignesVoyages($limit,$page, $filters)
    {
        if($page==1){$page=0;}
        $criterias=$this->buildCriterias($filters);
        $sql="select * from ligne_voyage as lv ,voyage as v, ville as vi WHERE lv.id_voyage=v.id AND v.id_ville=vi.id ".$criterias." ORDER BY dateDep LIMIT $limit OFFSET $page";
        $query = $this->db->query($sql);

        return $query->result("LigneVoyage");
    }
    function getLigneVoyage($id)
    {
        $query = $this->db->query('select * from ligne_voyage where id='.$id);
        if ($query->num_rows() > 0)
        {
            $lv=$query->first_row("LigneVoyage");
            $lv->placesRestantes = $lv->getPlacesRestantes();
            return $lv;
        }else{
           return null;
        }
    }
    function getVoyage()
    {
        $query = $this->db->query('select * from voyage where id='.$this->id_voyage);
        if ($query->num_rows() > 0)
        {
            return $query->first_row("Voyage");
        }else{
           return null;
        }
    }
    function getPlacesRestantes($id=null){
        $id=$id?$id:$this->id;
        $sqlImb="SELECT SUM(nbPlaces) FROM Reservation WHERE id_ligne_voyage=$id";
        $sql="select nbPlaces-($sqlImb) as nbPlacesRestantes from ligne_voyage where id=$id";
        $query = $this->db->query($sql);
        if ($query->num_rows() > 0)
        {
            return $query->first_row()->nbPlacesRestantes;
        }else{
           return $this->nbPlaces;
        }
    }
    function count($filters)
    {
        $criterias=$this->buildCriterias($filters);
        $query = $this->db->query('SELECT count(lv.id) as nb from ligne_voyage as lv ,voyage as v, ville as vi WHERE lv.id_voyage=v.id AND v.id_ville=vi.id  '.$criterias);
        if ($query->num_rows() > 0)
        {
            $res= $query->result();
            return $res[0]->nb;
        }else{
           return 0;
        }
    }
    
    private function buildCriterias($filters)
    {
        $criterias="";
        if($filters["annee"]!=""){
            $criterias.=" AND Year(lv.dateDep)=".$this->db->escape($filters["annee"])." ";
        }
        if($filters["mois"]!=""){
            $criterias.=" AND MONTH(lv.dateDep)=".$this->db->escape($filters["mois"])." ";            
        }
        if($filters["ville"]!=""){
            $criterias.=" AND v.id_ville=".$this->db->escape($filters["ville"])." ";            
        }
        if($filters["pays"]!=""){
            $criterias.=" AND vi.id_pays=".$this->db->escape($filters["pays"])." ";            
        }
        if($filters["type"]!=""){
            $criterias.=" AND v.id_type_voyage=".$this->db->escape($filters["type"])." ";                        
        }
        return $criterias;
    }
    
}

?>
